/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac4.servidor;

import edu.uoc.tdp.pac4.i18n.TDSLanguageUtils;
import edu.uoc.tdp.pac4.beans.Linia_peca;
import edu.uoc.tdp.pac4.beans.Marca_coche;
import edu.uoc.tdp.pac4.beans.Presup;
import edu.uoc.tdp.pac4.beans.Preu_peca;
import edu.uoc.tdp.pac4.beans.Proveidor;
import edu.uoc.tdp.pac4.beans.Tipus_peca;
import edu.uoc.tdp.pac4.comun.GestorInterface;
import edu.uoc.tdp.pac4.comun.Utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;


/**
 *
 * @author pguerreroj
 */
public class GestorInterfaceImpl extends UnicastRemoteObject implements GestorInterface, Serializable{
    
    private static final long serialVersionUID = 1L;
    public Connection conexion;
     final String propertiesFile = new File("").getAbsolutePath() + "//src//properties///configuration.properties";
    /* Linux Config */
    //final String propertiesFile = new File("").getAbsolutePath() + "/src/properties/configuration.properties";
    final String driver = "org.postgresql.Driver";
    
    
    public GestorInterfaceImpl() throws RemoteException, ExceptionBD {
        conectaBD();
    }

    public GestorInterfaceImpl(int paramInt) throws RemoteException, ExceptionBD {
        super(paramInt);
        conectaBD();
    }
  
    private void conectaBD() throws ExceptionBD {
        try {
            Class.forName("org.postgresql.Driver");
            Properties localProperties = new Properties();
            localProperties.load(new FileInputStream(this.propertiesFile));
            String str1 = localProperties.getProperty("url");
            String str2 = localProperties.getProperty("username");
            String str3 = localProperties.getProperty("password");
            
            
            this.conexion = DriverManager.getConnection(str1, str2, str3);
        } catch (ClassNotFoundException localClassNotFoundException) {
            Servidor.diplay(localClassNotFoundException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.JDBCKO"));
            
        } catch (FileNotFoundException localFileNotFoundException) {
            Servidor.diplay(localFileNotFoundException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.ConfigurationKO"));
        } catch (IOException localIOException) {
            Servidor.diplay(localIOException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.PropertiesKO"));
        } catch (SQLException localSQLException) {
            Servidor.diplay(localSQLException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.BBDDKO"));
        } catch (Exception localException) {
            Servidor.diplay(localException.getMessage());
            throw new ExceptionBD(localException.getMessage());
        }
    }

    @Override
    public ArrayList<Proveidor> getProveidors() throws RemoteException, SQLException {
        ArrayList<Proveidor> lista = new ArrayList<Proveidor>();
        Statement st = null;
        ResultSet result = null;
        try {
            st = this.conexion.createStatement(1004, 1008);
            String str = "SELECT * FROM PROVEIDOR";
            result = st.executeQuery(str);
            Servidor.diplay("ConsultaBD: " + str);
            while (result.next()) {
                Proveidor proveidor = new Proveidor();
                proveidor.setId_proveidor(result.getInt("id_proveidor"));
                proveidor.setNom(result.getString("nom"));
                proveidor.setPerc_descompte(result.getFloat("perc_descompte"));
                proveidor.setQuant_descompte(result.getInt("quant_descompte"));
                lista.add(proveidor);
                
            }
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return lista;
    }
    
    public Proveidor getProveidorById(int id) throws RemoteException, SQLException {
        Proveidor proveidor = new Proveidor();
        Statement st = null;
        ResultSet result = null;
        try {
            st = this.conexion.createStatement(1004, 1008);
            String str = "SELECT * FROM PROVEIDOR WHERE ID_PROVEIDOR=" + id;
            Servidor.diplay("ConsultaBD: " + str);
            result = st.executeQuery(str);
                proveidor.setId_proveidor(result.getInt("id_proveidor"));
                proveidor.setNom(result.getString("nom"));
                proveidor.setPerc_descompte(result.getFloat("perc_descompte"));
                proveidor.setQuant_descompte(result.getInt("quant_descompte"));
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return proveidor;
    }
    
    @Override
    public ArrayList<Marca_coche> getMarcas() throws RemoteException, SQLException {
        ArrayList<Marca_coche> lista = new ArrayList<Marca_coche>();
        Statement st = null;
        ResultSet result = null;
        try {
            st = this.conexion.createStatement(1004, 1008);
            String str = "SELECT * FROM MARCA_COTXE ";
            Servidor.diplay("ConsultaBD: " + str);
            result = st.executeQuery(str);
            while (result.next()) {
                Marca_coche marca = new Marca_coche();
                marca.setId_marca(result.getInt("id_marca"));
                marca.setNom(result.getString("nom"));
                lista.add(marca);
            }
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return lista;
    }

    @Override
    public ArrayList<Preu_peca> getPreusByProveidor(int proveidor) throws RemoteException, SQLException {
        ArrayList<Preu_peca> lista = new ArrayList<Preu_peca>();
        Statement st = null;
        ResultSet result = null;
        try {
            st = this.conexion.createStatement(1004, 1008);
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT PR.PREU, P.ID_PROVEIDOR,P.NOM,P.PERC_DESCOMPTE,P.QUANT_DESCOMPTE,M.ID_MARCA,M.NOM as MNOM,T.ID_TIPUS,T.NOM as TNOM ");
            sb.append("FROM PREU_PECA PR ");
            sb.append("INNER JOIN PROVEIDOR P ON PR.ID_PROVEIDOR = P.ID_PROVEIDOR ");
            sb.append("INNER JOIN MARCA_COTXE M ON PR.ID_MARCA = M.ID_MARCA ");
            sb.append("INNER JOIN TIPUS_PECA T ON PR.ID_TIPUS = T.ID_TIPUS ");
            sb.append("AND PR.ID_PROVEIDOR = " + proveidor + "ORDER BY PR.PREU DESC");
            Servidor.diplay("ConsultaBD: " + sb.toString());
            result = st.executeQuery(sb.toString());
            while (result.next()) {
                Preu_peca preuPeca = new Preu_peca();
                preuPeca.setPreu(result.getFloat("preu"));
                preuPeca.setProveidor(new Proveidor (result.getInt("id_proveidor"),result.getString("nom"),result.getFloat("perc_descompte"),result.getInt("quant_descompte")));
                preuPeca.setMarcaCoche(new Marca_coche (result.getInt("id_marca"),result.getString("mnom")));
                preuPeca.setTipoPeca(new Tipus_peca (result.getInt("id_tipus"),result.getString("tnom")));
                lista.add(preuPeca);                
            }
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return lista;
    }


    @Override
    public ArrayList<Preu_peca> getPreusByMarcaCoche(int marca) throws RemoteException, SQLException {
        ArrayList<Preu_peca> lista = new ArrayList<Preu_peca>();
        Statement st = null;
        ResultSet result = null;
        try {
            st = this.conexion.createStatement(1004, 1008);
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT PR.PREU, P.ID_PROVEIDOR,P.NOM,P.PERC_DESCOMPTE,P.QUANT_DESCOMPTE,M.ID_MARCA,M.NOM as MNOM,T.ID_TIPUS,T.NOM as TNOM ");
            sb.append("FROM PREU_PECA PR ");
            sb.append("INNER JOIN PROVEIDOR P ON PR.ID_PROVEIDOR = P.ID_PROVEIDOR ");
            sb.append("INNER JOIN MARCA_COTXE M ON PR.ID_MARCA = M.ID_MARCA ");
            sb.append("INNER JOIN TIPUS_PECA T ON PR.ID_TIPUS = T.ID_TIPUS ");
            sb.append("AND M.ID_MARCA = " + marca + " ORDER BY T.ID_TIPUS ASC");
            Servidor.diplay("ConsultaBD: " + sb.toString());
            result = st.executeQuery(sb.toString());
            while (result.next()) {
                Preu_peca preuPeca = new Preu_peca();
                preuPeca.setPreu(result.getFloat("preu"));
                preuPeca.setProveidor(new Proveidor (result.getInt("id_proveidor"),result.getString("nom"),result.getFloat("perc_descompte"),result.getInt("quant_descompte")));
                preuPeca.setMarcaCoche(new Marca_coche (result.getInt("id_marca"),result.getString("mnom")));
                preuPeca.setTipoPeca(new Tipus_peca (result.getInt("id_tipus"),result.getString("tnom")));
                lista.add(preuPeca);                
            }
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return lista;
    }

    @Override
     /* Mètode similar a getPreusByMarcaCoche però que només mostra un tipus de peca per
      * a cada marca
      */
    
    public ArrayList<Preu_peca> getPreusByMarcaCocheDistinct(int marca) throws RemoteException, SQLException {
        ArrayList<Preu_peca> lista = new ArrayList<Preu_peca>();
        Statement st = null;
        ResultSet result = null;
        try {
            st = this.conexion.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select distinct preu_peca.id_tipus as j_peca_id, tipus_peca.nom as j_peca_nom, tipus_peca.id_tipus, marca_cotxe.nom as j_marca_cotxe from preu_peca, tipus_peca, marca_cotxe where tipus_peca.id_tipus = preu_peca.id_tipus AND preu_peca.id_marca = marca_cotxe.id_marca AND preu_peca.id_marca = '"+marca+"' ORDER BY tipus_peca.nom");
            Servidor.diplay("ConsultaBD Distinct: " + sb.toString());
            result = st.executeQuery(sb.toString());
            while (result.next()) {
                Preu_peca preuPeca = new Preu_peca();
                preuPeca.setMarcaCoche(new Marca_coche (marca,result.getString("j_marca_cotxe")));
                preuPeca.setTipoPeca(new Tipus_peca (result.getInt("j_peca_id"),result.getString("j_peca_nom")));
                lista.add(preuPeca);                
            }
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
            }
        }
        return lista;
    }
    
    
    
	public Preu_peca getPreu_pecaById(int id) throws RemoteException, SQLException {
        
        Statement st = null;
        ResultSet result = null;
        Preu_peca preuPeca = new Preu_peca();
        try {
            st = this.conexion.createStatement(1004, 1008);
           // String query = "select distinct preu_peca.id_tipus,tipus_peca.nom from preu_peca, tipus_peca where tipus_peca.id_tipus = preu_peca.id_tipus AND preu_peca.id_peca = '"+id+"' ORDER BY tipus_peca.nom";
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT PR.PREU, P.ID_PROVEIDOR,P.NOM,P.PERC_DESCOMPTE,P.QUANT_DESCOMPTE,M.ID_MARCA,M.NOM as MNOM,T.ID_TIPUS,T.NOM as TNOM ");
            sb.append("FROM PREU_PECA PR ");
            sb.append("INNER JOIN PROVEIDOR P ON PR.ID_PROVEIDOR = P.ID_PROVEIDOR ");
            sb.append("INNER JOIN MARCA_COTXE M ON PR.ID_MARCA = M.ID_MARCA ");
            sb.append("INNER JOIN TIPUS_PECA T ON PR.ID_TIPUS = T.ID_TIPUS ");
            sb.append("AND M.ID_PECA = " + id + " ORDER BY T.ID_TIPUS ASC");
            Servidor.diplay("ConsultaBD: " + sb.toString());
            result = st.executeQuery(sb.toString());
            while (result.next()) {                
                preuPeca.setPreu(result.getFloat("preu"));
                preuPeca.setProveidor(new Proveidor (result.getInt("id_proveidor"),result.getString("nom"),result.getFloat("perc_descompte"),result.getInt("quant_descompte")));
                preuPeca.setMarcaCoche(new Marca_coche (result.getInt("id_marca"),result.getString("mnom")));
                preuPeca.setTipoPeca(new Tipus_peca (result.getInt("id_tipus"),result.getString("tnom")));
            }            
        } catch (SQLException e) {
            Servidor.diplay("Error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                result.close();
                st.close();
            } catch (Exception e) {
                Servidor.diplay("Error: " + e.getMessage());
                e.printStackTrace();
            }
        }
        return preuPeca;
    }
    
        /* Funció que fa el càlcul de l'import de les peces seleccionades i retorna el
         * text amb el missatge
         */
        
        
    public String calculaPressupost(ArrayList<Linia_peca> comanda) throws RemoteException, SQLException {
  
        Statement st = null;
        ResultSet result = null;
        String textMostrar = "" ;
        String textUnidades="";
        int unitatsTotal = 0;
        st = this.conexion.createStatement(1004, 1008);

        /* Cerquem el llistat de proveídors */
        
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM proveidor");
        Servidor.diplay("ConsultaBD: " + sb.toString());
        result = st.executeQuery(sb.toString());
        
        while(result.next()){
                Proveidor p = new Proveidor(result.getInt("id_proveidor"), result.getString("nom"), result.getFloat("perc_descompte"), result.getInt("quant_descompte"));
        
               /* Creem un objecte Presup (ost) que contindrà la peça, les unitats demanades 
               * i l'import total (amb descompte o sense)
               */
                   
               Presup pr = new Presup();               
               float importTotal = 0;
               
               /* Controla si un proveïdor té la peça o no
               * Si una de les peces demanades no està, no es dóna pressupost
               */
               boolean ok = true;
               unitatsTotal = 0;    
                /* Fem un recorregut de la comanda */    
                for(int i = 0; i < comanda.size() ; i++ ){
                    
                    Tipus_peca peca = comanda.get(i).getPreuPeca().getTipoPeca();
                    unitatsTotal = unitatsTotal+comanda.get(i).getUnitats();
                    
                    /* Recuperem el preu d'una peça a un proveïdor donada amb la marca i el proveïdpr */
                    float f = proveidorTePeca(p, comanda.get(i).getPreuPeca().getTipoPeca(), comanda.get(i).getMarca());
                    if(f == 0.0) {
                        /* El proveïdor no té totes les peces */
                        ok = false;
                    } else {
                        /* Afegim les peces al pressupost per tal que faci el càlcul */
                        pr.addImport(proveidorTePeca(p, peca, comanda.get(i).getMarca()), comanda.get(i).getUnitats());
                        
                    }

                    /* Comprovem si tenim descompte en funció del nombre de peces */
                   if(p.permetDescompte(unitatsTotal)) {
                        importTotal = p.descuento(pr.total, unitatsTotal);
                   } else {
                        importTotal = pr.total;
                   }
                }

                /* Omplim el missatge */
                if(ok==true) {
                            textMostrar += TDSLanguageUtils.getMessage("Presupuesto.Proveedor")+" : "+p.getNom()+"  | "+TDSLanguageUtils.getMessage("Presupuesto.Subtotal")+" : "+ Utils.formato(pr.total,2)+"  | "+TDSLanguageUtils.getMessage("Presupuesto.Total")+" : "+ Utils.formato(importTotal,2) +"     \n";
                                }
            }
            
             if(textMostrar.length()==0) {
                            textMostrar = TDSLanguageUtils.getMessage("Presupuesto.NoPresupuesto"); 
             }
        textUnidades += TDSLanguageUtils.getMessage("Presupuesto.Unidades")+ unitatsTotal + "\n" + textMostrar;
        return textUnidades;
  
	}    
  
  
    public float proveidorTePeca(Proveidor p, Tipus_peca pc, int idMarca) {
        float preu = 0;
        Statement st = null;
        ResultSet result = null;
        try {
            st = conexion.createStatement(1004, 1008);
            StringBuffer sb = new StringBuffer();
            sb.append("select preu_peca.id_tipus,tipus_peca.nom, preu_peca.id_marca, preu_peca.id_proveidor, preu_peca.preu as j_preu from preu_peca, tipus_peca where tipus_peca.id_tipus = preu_peca.id_tipus AND preu_peca.id_marca = '"+idMarca+"' AND preu_peca.id_proveidor = '"+p.getId_proveidor()+"' and preu_peca.id_tipus = '"+pc.getId_tipus()+"'  ORDER BY tipus_peca.nom");
            Servidor.diplay("ConsultaBD: " + sb.toString());
            result = st.executeQuery(sb.toString());

            while(result.next()){
              preu = result.getFloat("j_preu");
            }            
            
        } catch (SQLException e) {
            Servidor.diplay("Error en ConsultaBD: " + e.getMessage());
            preu = 0;
        }
        return preu;
    }
    
}
